客户反馈在销售订单下推生产订单时，耗时1mins左右。使用sql server profiler抓取sql，分析执行计划发现主要消耗如下：
初始sql执行计划
从执行计划中，找到对应的sql脚本如下：
-- 1排除服务物料或者不允许生产的物料
AND NOT EXISTS (
SELECT 1
FROM T_BD_MaterialBase MB
INNER JOIN T_BD_MATERIAL MBTA ON MB.FMATERIALID = MBTA.FMATERIALID
WHERE (
(
(
MB.FErpClsID = '6'
OR FIsProduce = '0'
)
AND MBTA.FUSEORGID = t2.FSUPPLYORGID
)
AND EXISTS (
SELECT 1
FROM T_BD_MATERIAL MTR
WHERE (
MTR.FMATERIALID = t2.FMATERIALID
AND MTR.FMASTERID = MBTA.FMASTERID
)
)
)
) )
-- 2 排除收款计划中控制环节为生产且实收与应收不等（即收款计划中控制环节为生产且未收款完成的订单不允许生产）
AND NOT EXISTS (
SELECT 1
FROM t_sal_orderPlan a
INNER JOIN (
SELECT sum(a.FRECADVANCEAMOUNT) frecadvanceamount
,sum(a.FRecAmount) frecamount
,a.FID
FROM t_sal_OrderPlan a
INNER JOIN t_sal_orderPlan b ON (
a.FID = b.FID
AND (a.FSeq <= b.FSeq)
)
WHERE (
b.FControlSend = 'PRODUCT'
AND a.FID = b.FID
)
GROUP BY a.FID
) b ON a.FID = b.FID
WHERE (
(b.Frecadvanceamount <> b.FRecAmount)
AND a.FId = t0.FID
)
)
执行计划中显示【T_SAL_ORDERPLAN】表子查询为【聚集索引扫描】，针对输出列，在【T_SAL_ORDERPLAN】表中添加索引。再次执行，耗时还是1mins左右，查看执行计划，【聚集索引扫描】已经优化成【索引查找】，但优化效果不明显。
添加索引后
执行计划分析无果，只得对sql语句进行分析，捋一捋业务逻辑。
先来看一下第一段逻辑
-- 1排除服务物料或者不允许生产的物料
AND NOT EXISTS (
SELECT 1
FROM T_BD_MaterialBase MB
INNER JOIN T_BD_MATERIAL MBTA ON MB.FMATERIALID = MBTA.FMATERIALID
WHERE (
(
(
MB.FErpClsID = '6'
OR FIsProduce = '0'
)
AND MBTA.FUSEORGID = t2.FSUPPLYORGID
)
AND EXISTS (
SELECT 1
FROM T_BD_MATERIAL MTR
WHERE (
MTR.FMATERIALID = t2.FMATERIALID
AND MTR.FMASTERID = MBTA.FMASTERID
)
)
)
) )
该段SQL目的是排除服务物料或者不允许生产的物料。反过来，就是只过滤显示【非服务且允许生产的物料】，改造SQL如下：
AND EXISTS (
SELECT 1
FROM T_BD_MATERIAL MBTA
INNER JOIN T_BD_MaterialBase MB ON MB.FMATERIALID = MBTA.FMATERIALID
WHERE MB.FErpClsID <> '6'
AND MB.FIsProduce = '1'
AND MBTA.FUSEORGID = FSupplyOrgId
AND EXISTS (
SELECT 1
FROM T_BD_MATERIAL MTR
WHERE (
MTR.FMATERIALID = t2.FMATERIALID
AND MTR.FMASTERID = MBTA.FMASTERID
)
)
)
Not exists转换为exists
从执行计划上看，优化结果不明显。
寻求分析第二段代码：
-- 2 排除收款计划中控制环节为生产且实收与应收不等（即收款计划中控制环节为生产且未收款完成的订单不允许生产）
AND NOT EXISTS (
SELECT 1
FROM t_sal_orderPlan a
INNER JOIN (
SELECT sum(a.FRECADVANCEAMOUNT) frecadvanceamount
,sum(a.FRecAmount) frecamount
,a.FID
FROM t_sal_OrderPlan a
INNER JOIN t_sal_orderPlan b ON (
a.FID = b.FID
AND (a.FSeq <= b.FSeq)
)
WHERE (
b.FControlSend = 'PRODUCT'
AND a.FID = b.FID
)
GROUP BY a.FID
) b ON a.FID = b.FID
WHERE (
(b.Frecadvanceamount <> b.FRecAmount)
AND a.FId = t0.FID
)
)
仔细一看，这什么鬼，【t_sal_OrderPlan】表，与自身inner join了三次，相当于嵌套查询三次。优化为：
-- 排除收款计划中控制环节为生产且实收与应收不等（即收款计划中控制环节为生产且未收款完成的订单不允许生产）
AND NOT EXISTS (
--SELECT 1
--FROM t_sal_orderPlan a
--INNER JOIN (
SELECT a.FID
--,sum(a.FRECADVANCEAMOUNT) frecadvanceamount
--,sum(a.FRecAmount) frecamount
FROM t_sal_OrderPlan a
WHERE a.FControlSend = 'PRODUCT'
AND a.fid = t0.fid
GROUP BY a.FID
HAVING sum(a.FRECADVANCEAMOUNT) <> sum(a.FRecAmount)
--) b ON a.FID = b.FID
--WHERE a.fid = t0.fid
)
这一改不要紧，内部查询是快了，返回空，但是与外部关联嵌套查询，直接几分钟没出来结果。
只能寻求其他优化，分析逻辑大致是【排除收款计划中控制环节为生产且实收与应收不等（即收款计划中控制环节为生产且未收款完成的订单不允许生产）】。取反：
- 过滤显示收款计划中控制环节为非生产的
- 过滤收款计划中控制环节为生产但收款完成的
优化sql如下：
-- 1. 过滤显示收款计划中控制环节为非生产的
AND EXISTS (
SELECT 1
FROM T_SAL_ORDERPLAN a
WHERE a.FCONTROLSEND <> 'PRODUCT'
AND a.fid = t0.fid
UNION ALL
-- 2. 过滤收款计划中控制环节为**生产但收款完成**的
--SELECT 1 FROM t_sal_orderPlan c
-- INNER JOIN (
SELECT a.FID
--,sum(a.FRECADVANCEAMOUNT) frecadvanceamount
--,sum(a.FRecAmount) frecamount
FROM t_sal_OrderPlan a
WHERE a.FControlSend = 'PRODUCT'
AND a.FID = t0.fid
GROUP BY a.FID
HAVING sum(a.FRECADVANCEAMOUNT) <= sum(a.FRecAmount)
--) b ON c.FID = b.FID
--where c.fid = t0.fid
)
优化后，时间缩减为10s内，优化结果可以接受。优化后的执行计划为：
最终优化
针对这次优化，有以下疑问：
- Not exists与exists性能问题
- 优化后，执行计划报
No join predicate。
